package com.hezuti.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

public class DBManager {

	//	 数据库连接对�?  
    private Connection con;
    //  SQL语句对象   
    private Statement stmt;  
    //  带参数的Sql语句对象   
    private PreparedStatement pstmt;   
    // 记录集对�?  
    private ResultSet rs;   
	/**  
     * 从指定参数得到一个连接对�? 
     *   
     * @param driver  
     * @param url  
     * @param user_na  
     * @param password  
     * @throws Exception  
     */  
    public void getConnection(String driver, String url, String user_na,   
            String password)  {   
        try {    
            Class.forName(driver);   
            con = DriverManager.getConnection(url, user_na, password); 
            con.setAutoCommit(false);
        } catch (ClassNotFoundException ex) {   
              
            ex.printStackTrace(); 
        } catch (SQLException ex) {   
            
            ex.printStackTrace();   
        }   
    }
    
    public void getConnection(String properties) {
    	PropertyReader pr = new PropertyReader(properties);
    	String driver = pr.getValue("driver");
    	String url = pr.getValue("url");
    	String username = pr.getValue("username");
    	String password = pr.getValue("password");
    	
    	getConnection(driver,url,username,password);
    }
    
    public void getConnection() {
    	PropertyReader pr = new PropertyReader("config.properties");
    	String driver = pr.getValue("driver");
    	String url = pr.getValue("url");
    	String username = pr.getValue("username");
    	String password = pr.getValue("password");
    	
    	getConnection(driver,url,username,password);
    }
    /**  
     * 执行SQL语句操作(更新数据 无参�?  
     *   
     * @param strSql  
     *            SQL语句  
     * @throws Exception  
     */  
    public boolean executeUpdate(String strSql) throws SQLException {   
          
        boolean flag = false;   
        stmt = con.createStatement();   
       
        try {   
            if (0 < stmt.executeUpdate(strSql)) {   
//                close_DB_Object();   
                flag = true;   
                con.commit();   
            }   
        } catch (SQLException ex) {   
            
            flag = false;   
            con.rollback();   
            throw ex;   
        }   
        return flag;   
  
    }  
    
    /**  
     * 执行SQL语句操作(更新数据 有参�?  
     *   
     * @param strSql  
     *            sql指令  
     * @param prams  
     *            参数列表  
     * @return  
     * @throws SQLException  
     */  
	public boolean executeUpdate(String strSql, HashMap<Integer, Object> prams)   
            throws SQLException, ClassNotFoundException {   
        
        boolean flag = false;   
        try {   
            pstmt = con.prepareStatement(strSql);   
            setParamet(pstmt, prams);    
  
            if (0 < pstmt.executeUpdate()) {   
//                close_DB_Object();   
                flag = true;   
                con.commit();   
            }   
        } catch (SQLException ex) {   
            flag = false;   
            con.rollback();   
            throw ex;   
        } catch (ClassNotFoundException ex) {   
            
            con.rollback();   
            throw ex;   
        }   
        return flag;   
  
    }  
    
    /**  
     * 执行SQL语句操作(更新数据 无参�?  
     *   
     * @param strSql  
     *            SQL语句  
     * @throws Exception  
     */  
    public boolean execute(String strSql) throws SQLException {   
          
        boolean flag = false;   
        stmt = con.createStatement();   
       
        try {   
        	System.out.println("start execute");
            if (stmt.execute(strSql)) {   
//                close_DB_Object(); 
            	System.out.println("execute success");
                flag = true;   
                con.commit();  
                return flag;
            }   
        } catch (SQLException ex) {   
            
            flag = false;   
            con.rollback();   
            throw ex;   
        }   
        return flag;   
  
    }  
    
    /**  
     * 执行SQL语句操作(更新数据 有参�?  
     *   
     * @param strSql  
     *            sql指令  
     * @param prams  
     *            参数列表  
     * @return  
     * @throws SQLException  
     */  
    public boolean execute(String strSql, HashMap<Integer, Object> prams)   
            throws SQLException, ClassNotFoundException {   
        
        boolean flag = false;   
        try {   
            pstmt = con.prepareStatement(strSql);   
            setParamet(pstmt, prams);    
  
            if (pstmt.executeUpdate()!=0) {   
//                close_DB_Object();   
                flag = true;   
                con.commit();   
            }   
        } catch (SQLException ex) {   
            flag = false;   
            con.rollback();   
            throw ex;   
        } catch (ClassNotFoundException ex) {   
            
            con.rollback();   
            throw ex;   
        }   
        return flag;   
  
    }  
    
    /**  
     * 执行SQL语句操作(查询数据 无参�?  
     *   
     * @param strSql  
     *            SQL语句  
     * @return 数组对象列表  
     * @throws Exception  
     */  
    public  ResultSet executeSql(String strSql)   
            throws Exception {   
         
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);   
          
        rs = stmt.executeQuery(strSql);   
        
      
        con.commit();   
        if (null != rs) {   
            return rs;   
        }   
//        close_DB_Object();   
        return null;   
    }   

    /**  
     * 执行SQL语句操作(查询数据 无参�?  
     *   
     * @param strSql  
     *            SQL语句  
     * @return 数组对象列表  
     * @throws Exception  
     */  
    public  ResultSet executeSql1(String strSql)   
            throws Exception {   
         
        stmt = con.createStatement();   
          
        rs = stmt.executeQuery(strSql);   
      
        con.commit();   
        if (null != rs) {   
            return rs;   
        }   
//        close_DB_Object();   
        return null;   
    }   
    
    /**  
     * 执行SQL语句操作(查询数据 有参�?  
     *   
     * @param strSql  
     *            SQL语句  
     * @param prams  
     *            参数列表  
     * @return 数组对象列表  
     * @throws Exception  
     */  
    public ResultSet executeSql(String strSql,   
            HashMap<Integer, Object> prams) throws Exception {   
          
        pstmt = con.prepareStatement(strSql);   
        setParamet(pstmt, prams);   
           
        rs = pstmt.executeQuery();   
        
        con.commit();   
        if (null != rs) {   
            return rs;   
        }   
        return null;   
    }   

    /**  
     * 关闭数据对象  
     */  
    public void close_DB_Object() {     
        if (null != rs) {   
            try {   
                rs.close();   
            } catch (SQLException ex) {   
                rs = null;   
            }   
        }   
        if (null != stmt) {   
            try {   
                stmt.close();   
            } catch (SQLException ex) {   
                stmt = null;   
            }   
        }   
        if (null != pstmt) {   
            try {   
                pstmt.close();   
            } catch (SQLException ex) {   
                pstmt = null;   
            }   
        }   
        if (con != null) {   
        	try {   
        		con.close();   
            } catch (SQLException ex) {   
                pstmt = null;   
            }   
        }   
    } 
    
    /**  
     * 设置Sql 指令参数  
     *   
     * @param p_stmt  
     *            PreparedStatement  
     * @param pramets  
     *            HashMap  
     */  
    private PreparedStatement setParamet(PreparedStatement p_stmt,   
            HashMap<Integer, Object> pramets) throws ClassNotFoundException,   
            SQLException {   
        // 如果参数为空   
        if (null != pramets) {   
            // 如果参数个数�?   
            if (0 <= pramets.size()) {   
                for (int i = 1; i <= pramets.size(); i++) {   
                    try {   
                        // 字符类型 String   
                        if (pramets.get(i).getClass() == Class   
                                .forName("java.lang.String")) {   
                            p_stmt.setString(i, pramets.get(i).toString());   
                        }   
                        // 日期类型 Date   
                        if (pramets.get(i).getClass() == Class   
                                .forName("java.sql.Date")) {   
                            p_stmt.setDate(i, java.sql.Date.valueOf(pramets   
                                    .get(i).toString()));   
                        }   
                        // 布尔类型 Boolean   
                        if (pramets.get(i).getClass() == Class   
                                .forName("java.lang.Boolean")) {   
                            p_stmt.setBoolean(i, (Boolean) (pramets.get(i)));   
                        }   
                        // 整型 int   
                        if (pramets.get(i).getClass() == Class   
                                .forName("java.lang.Integer")) {   
                            p_stmt.setInt(i, (Integer) pramets.get(i));   
                        }   
                        // 浮点 float   
                        if (pramets.get(i).getClass() == Class   
                                .forName("java.lang.Float")) {   
                            p_stmt.setFloat(i, (Float) pramets.get(i));   
                        }   
                        // 双精度型 double   
                        if (pramets.get(i).getClass() == Class   
                                .forName("java.lang.Double")) {   
                            p_stmt.setDouble(i, (Double) pramets.get(i));   
                        }   
  
                    } catch (ClassNotFoundException ex) {   
                        throw ex;   
                    } catch (SQLException ex) {   
                        throw ex;   
                    }   
                }   
            }   
        }   
        return p_stmt;   
    }  
	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		DBManager dbm = new DBManager();
		dbm.getConnection();
		ResultSet rs = dbm.executeSql("select * from book");
		while(rs.next()){
			System.out.println(rs.getString("id"));
		}
		dbm.close_DB_Object();
	}

}
